/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
import java.sql.*;
/**
 * 
 * @authors: Pat, Jamie, Kazu, Stephen, and Shaoyan
 */
public class Customer {

    private DB db;
    //private Connection conn;

    /**
     * 
     */
    public Customer() {
        this.db = new DB();
        //this.conn = db.connect();
    }
    
    /**
     * 
     * @param username
     * @param password
     * @return
     */
    public int login(String username, String password) {
        int userType = -1;
        Connection conn = db.connect();
        String query = "SELECT password, userType FROM UserDB WHERE username = '" + username+"'";
        ResultSet result = db.getQuery(conn,query);
        try{
            result.next();
            String correctPassword = result.getString("password");
            if (password.equals(correctPassword))
                userType = result.getInt("userType");
        }catch(Exception e){ }
        db.disconnect(conn);
        System.out.println(userType);
        return userType;
    }
    
    /**
     * 
     * @param username
     * @return
     */
    public int getCustomerId(String username) {
        int custId = -1;
        Connection conn = db.connect();
        String query = "SELECT customerId FROM UserDB WHERE username = '" + username + "'";
        ResultSet result = db.getQuery(conn,query);
        try{
            result.next();
            custId = result.getInt("customerId");
        }catch(Exception e){ }
        db.disconnect(conn);
        return custId;
    }
    
    public int getUserType(String username){
        int _userType = -1;
        Connection conn = db.connect();
        String query = "SELECT userType FROM UserDB WHERE username = '" + username + "'";
        ResultSet result = db.getQuery(conn,query);
        try{
            result.next();
            _userType = result.getInt("userType");
        }catch(Exception e){ }
        db.disconnect(conn);
        return _userType;
    }
    
    /**
     * 
     * @param username
     * @return
     */
    public String getCustomerName(String username){
        String name = null;
        Connection conn = db.connect();
        String query = "SELECT customerName FROM UserDB WHERE username = '" + username + "'";
        ResultSet result = db.getQuery(conn,query);
        try{
            result.next();
            name = result.getString("customerName");
        }catch(Exception e){ }
        db.disconnect(conn);
        return name; 
    }

    /**
     * 
     * @param custID
     * @return
     */
    public String getExpiryDate(int custID){
        String date = "";
        Connection conn = db.connect();
        String query = "SELECT membershipEndDate FROM UserDB WHERE customerId = " + custID;
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        try{
            result.next();
            date = result.getString("membershipEndDate");
        }catch(Exception e){ }
        return date;
    }

    /**
     * 
     * @param custID
     * @return
     */
    public int getHoursLeft(int custID){
        int hours = -1;
        Connection conn = db.connect();
        String query = "SELECT hoursLeft FROM UserDB WHERE customerId = " + custID;
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        try{
            result.next();
            hours = result.getInt("hoursLeft");
        }catch(Exception e){ }
        return hours;
    }

    /**
     * Updates the remaining hours for a customer.
     * @param custID
     * @param hours, the number of hours to INCREASE the remaining hours by.
     */
    public void updateHours(int custID, int hours){
        Connection conn = db.connect();
        int currentHours = getHoursLeft(custID);
        String query = "UPDATE UserDB SET hoursLeft = " + (currentHours+hours)
                + " WHERE customerId = " + custID;
        db.putQuery(conn, query);
        db.disconnect(conn);
    }

    /**
     * 
     * @param custID
     * @return
     */
    public String[] getCustomerInfo(int custID){
        String[] info = new String[12];
        Connection conn = db.connect();
        String query = "SELECT * FROM UserDB WHERE customerId = " + custID;
        ResultSet result = db.getQuery(conn,query);
        db.disconnect(conn);
        try{
            result.next();
            info[0] = result.getInt("customerId")+"";
            info[1] = result.getString("username");
            info[2] = result.getString("password");
            info[3] = result.getString("customerName");
            info[4] = result.getInt("hoursLeft")+"";
            info[5] = result.getString("membershipStartDate");
            info[6] = result.getString("membershipEndDate");
            info[7] = result.getString("email");
            info[8] = result.getString("address");
            info[9] = result.getString("phoneNumber");
            info[10]= result.getInt("userType")+"";
            info[11]= result.getString("paymentInfo");
        }catch(Exception e){ }
        return info;
    }
    
    /**
     * 
     * @param custID
     * @param email
     * @param address
     * @param phone
     */
    public void updateCustomerContactInfo(int custID,String email, String address, String phone){
        // ' characters mess up the SQU query, so I get rid of them here.
        address.replace("'", "");
        email.replace("'", "");
        phone.replace("'", "");
        Connection _conn = db.connect();
        String query = "UPDATE UserDB SET address = '" + address 
                +"', phoneNumber = '" +phone
                +"', email = '"+email
                +"' WHERE customerId = " + custID;
        db.putQuery(_conn, query);
        db.disconnect(_conn);
    }
    
    public void updatePaymentInfo(int custID,String payment){
        Connection _conn = db.connect();
        String query = "UPDATE UserDB SET paymentInfo = '" + payment 
                +"' WHERE customerId = " + custID;
        db.putQuery(_conn, query);
        db.disconnect(_conn);
    }
    
    public void updateMembershipInfo(int custID, String expDate, int hoursR){
        expDate.replace("'", "");
        Connection _conn = db.connect();
        String query = "UPDATE UserDB SET membershipEndDate = '" + expDate 
                +"', hoursLeft = " + hoursR
                +" WHERE customerId = " + custID;
        db.putQuery(_conn, query);
        db.disconnect(_conn);
        System.out.println("done update");
    }
    
    public int lookupCustomer(String name, String phone) {
        
        int customerId = -1;
        Connection conn = db.connect();
        ResultSet result;
        
        if (!name.isEmpty()) {
            String query = "SELECT customerId FROM UserDB WHERE customerName = '" + name + "'";
            result = db.getQuery(conn,query);

            try {
                result.next();
                customerId = result.getInt("customerId");
            } catch(Exception e){ }
        } else {
            String query = "SELECT customerId FROM UserDB WHERE phoneNumber = " + phone;
            result = db.getQuery(conn,query);
            
            try {
                result.next();
                customerId = result.getInt("customerId");
            } catch(Exception e){ }
        }
        db.disconnect(conn);
        
        return customerId;
    }
    
    public int addCustomer(String username, String password, String customerName, String StartDate, 
                            String EndDate, String email, String address, String phoneNumber, String paymentInfo) {
        int userType = 0; // 0 for user type customer
        int hoursLeft = 96; // default number for new customers
        
        Connection conn = db.connect();
        String query = "INSERT INTO UserDB (username, password, customerName, hoursLeft, membershipStartDate, membershipEndDate, email, address, phoneNumber, userType, paymentInfo) "
                +"VALUES ('" + username + "','" + password + "','" + customerName + "','" + hoursLeft + "','" + StartDate + "','" + EndDate + "','" + email + "','" + address + "','" + phoneNumber + "','" + userType + "','" + paymentInfo + "')";
        db.putQuery(conn, query);
        db.disconnect(conn);
        
        return 0;
    }
}